{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel布局结构</font>**<br/>\n",
    "<img src=\"images/Excel_Layout.jpg\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1、导入读取Excel的工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# xlrd 工具包可以对 xlsx、xls、xlsm 文件进行读操作\n",
    "# xlrd 的命名是 excel reader 简写，很容易记忆\n",
    "import xlrd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2、读取 Excel 文件，并得到工作簿（workbook）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开 Excel 工作簿，需要指定 Excel 文件的路径名\n",
    "workbook = xlrd.open_workbook(\"./files/Excel_读取工资单.xls\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3、从工作簿中获取第一个工作表（sheet）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取第一个工作表，从0开始\n",
    "sheet = workbook.sheet_by_index(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4、读取工作表中的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从第一个工作表中读取第一行数据（注意：从0开始为第一个）\n",
    "sheet.row_values(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读第2行数据\n",
    "sheet.row_values(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 提问：一行行的代码去读数据显然不是好办法，怎么办？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 得要用循环的 Python 语法\n",
    "# print 的用法\n",
    "for i in range(4):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 我们可以看看工作表有多少行数据\n",
    "print(sheet.nrows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从 0 行 开始，使用循环解决\n",
    "for row_index in range(sheet.nrows):\n",
    "    row_values = sheet.row_values(row_index)  # 获取 row_index 行内容\n",
    "    # 打印这一行数据列表\n",
    "    print(row_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 什么是列表？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_list = [1, 2, 3]  # 创建一个列表 1 到 3\n",
    "print(my_list)\n",
    "print(my_list[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python总结"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4>1、导入工具包</font>**\n",
    "\n",
    "<font color=red size=4>import xlrd</font>\n",
    "\n",
    "<font color=blue size=4>我们在使用一个功能的时候，都需要导入相应的工具包。Python提供的工具包非常多，一辈子都学不完，但是没有关系，我们只需关注与我们工作相关的工具包即可</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4>2、print 的用法</font>**\n",
    "\n",
    "<font color=blue size=4>将信息打印出来</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4>3、对象名称 + 点 + 成员名称（对象.成员名）</font>**\n",
    "\n",
    "**<font color=blue size=4>比如：</font>**\n",
    " \n",
    "<font color=red size=4>1）workbook.sheet_by_index(0)  </font>\n",
    "\n",
    "<font color=blue size=4>读取工作簿对象<font color=red size=4 style=background-color:yellow>(workbook)</font>的第一个<font color=red size=4 style=background-color:yellow>(index=0)</font>工作表<font color=red size=4 style=background-color:yellow>(sheet)</font></font>\n",
    "\n",
    "<font color=red size=4>2）sheet.row_values(0) </font>\n",
    "\n",
    "  \n",
    "<font color=blue size=4> 读取工作表<font color=red size=4 style=background-color:yellow>(sheet)</font>第一行<font color=red size=4 style=background-color:yellow>(0)</font>的数据值<font color=red size=4 style=background-color:yellow>(row_values)</font></font>\n",
    "\n",
    "\n",
    "<font color=red size=4>3）sheet.nrows </font>\n",
    "\n",
    "<font color=blue size=4> 获取工作表<font color=red size=4 style=background-color:yellow>(sheet)</font>的行数<font color=red size=4 style=background-color:yellow>(nrow=Number of rows)</font></font>\n",
    "\n",
    "<font color =blue size=4>有括号称为方法<font color=red size=4>(function)</font>、没有括号称为属性<font color=red size=4>(property)</font>，统统称为成员<font color = red size=4>(member)</font></font>\n",
    "\n",
    "\n",
    "<font color=blue size=4>我们称为以上的编程方式为<font color=red size=4>面向对象</font>的编程</font>\n",
    "\n",
    "<font color=blue size=4>比如：猫作为一个<font color=red size=4>对象</font>，猫的颜色就是<font color=red size=4>属性</font>、猫吃老鼠就是<font color=red size=4>方法</font>，对应的代码为 <font color=red size=4>cat.color</font> 和 <font color=red size=4>cat.eat(mouse)</font> </font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 4、赋值并命名</font>**\n",
    "\n",
    "**<font color=blue size=4>比如：</font>**\n",
    "\n",
    "<font color=red size=4> sheet = workbook.sheet_by_index(0) </font>\n",
    "\n",
    "<font color=blue size=4>读取工作簿对象<font color=red size=4 style=background-color:yellow>(workbook)</font>的第一个工作表并赋值给<font color=red size=4 style=background-color:yellow>(sheet)</font></font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 5、循环 for i in range </font>**\n",
    "\n",
    "**<font color=blue size=4>比如：</font>**\n",
    "\n",
    "<font color=red size=4> for row_index in range(sheet.nrows):</font>\n",
    "\n",
    "<font color=red size=4>&nbsp;&nbsp;&nbsp;row_values = sheet.row_values(row_index)  <font color=gray size=4># 获取row_index行内容（注释）</font></font>\n",
    "\n",
    "<font color=red size=4>&nbsp;&nbsp;&nbsp;print(row_values)</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 6、列表的使用 </font>**\n",
    "\n",
    "<font color=red size=4> my_list = [1, 2, 3] </font><font color=gray size=4> # 创建一个列表 1 到 3</font>\n",
    "\n",
    "<font color=red size=4> print(my_list[0]) </font><font color=gray size=4> # 获取第一个列表的第一项 </font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 7、代码的注释 </font>**\n",
    "\n",
    "<font color=blue size=4>注释的主要作用是用说明文字来辅助人类<font color=red size=4 style=background-color:yellow>更好的理解代码</font>，机器能理解代码，但是不能理解说明文字，所以我们在说明文字的前面加一个字符<font color=red size=4 style=background-color:yellow>#</font>，机器看到<font color=red size=4 style=background-color:yellow>#</font>就会忽略掉</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 完整代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# xlrd 工具包可以对 xlsx、xls、xlsm 文件进行读操作\n",
    "# xlrd 的命名是 excel reader 简写，很容易记忆\n",
    "import xlrd\n",
    "# 打开 Excel 工作簿，需要指定 Excel 文件的路径名\n",
    "workbook = xlrd.open_workbook(\"./files/Excel_读取工资单.xls\")\n",
    "# 获取第一个工作表，从0开始\n",
    "sheet = workbook.sheet_by_index(0)\n",
    "# 从 0 行 开始，使用循环解决\n",
    "for row_index in range(sheet.nrows):\n",
    "    row_values = sheet.row_values(row_index)  # 获取row_index行内容\n",
    "    # 打印这一行数据列表\n",
    "    print(row_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 思考题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用Python代码实现打印 sheet 第3行、第3列的数据\n",
    "print(sheet.row_values(2))\n",
    "print(sheet.row_values(2)[2])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
